﻿CREATE PROCEDURE [util].[CheckDevice]
@DeviceName [sysname], @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @RC int;
	SET @RC = 0;
	
	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
		END
	
	DECLARE @FileCount int=0;

	DECLARE DEVICE_GROUP CURSOR FAST_FORWARD FOR(
		SELECT [phyname] 
		FROM master.dbo.sysdevices
		WHERE ([name] LIKE @DeviceName + N'.%') --ignore parallel index no.
	);
	DECLARE @PhysicalPath nvarchar(260);
	OPEN DEVICE_GROUP;
	FETCH NEXT FROM DEVICE_GROUP INTO @PhysicalPath;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		DECLARE @Exists int;
		EXEC master.dbo.xp_fileexist @PhysicalPath, @Exists OUTPUT
		IF(@Exists <> 1)
			BEGIN
				RAISERROR('Physical file (%s) for device %s does not exist',16,1,@PhysicalPath,@DeviceName)
				SET @RC = 2
			END

		DECLARE @TestReadStatement NVARCHAR(255);
		SET @TestReadStatement = N'RESTORE FILELISTONLY FROM DISK=''' + @PhysicalPath + '''';
		EXEC @RC = sp_executesql @TestReadStatement;
		IF(@RC <> 0)
			RAISERROR('Physical file (%s) for device %s can not be read',16,1,@PhysicalPath,@DeviceName);

		SET @FileCount = @FileCount + 1;

		IF(@OutputDiagnostics = 1)
			PRINT N'	@PhysicalPath(' + CAST(@FileCount AS nvarchar) + ') = N''' + ISNULL(@PhysicalPath,N'NULL') + '''';

		FETCH NEXT FROM DEVICE_GROUP INTO @PhysicalPath;
	END
	CLOSE DEVICE_GROUP;
	DEALLOCATE DEVICE_GROUP;
				
	IF(@FileCount = 0)
		BEGIN
			RAISERROR('Device %s does not exist',16,1,@DeviceName)
			SET @RC = 1
			GOTO EXIT_PROC
		END

EXIT_PROC:
	RETURN(@RC)
END